﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;
namespace DAO
{
    public class LoaiMonAnDAO
    {
        //lay ma mon an
        public int LayMa(string table, string ma)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select top 1 " + ma + " from " + table + " order by " + ma + " desc";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;

            int kq;
            try
            {
                kq = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            return kq;
        }
        //lay danh sach loai mon an
        public List<LoaiMonAnDTO> LayDanhSachLoaiMonAn()
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<LoaiMonAnDTO> KQ = new List<LoaiMonAnDTO>();
            string sql = "select * from LoaiMonAn";
            SqlDataAdapter dtadp = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable dt = new DataTable();
                dtadp.Fill(dt);
                LoaiMonAnDTO cn;
                foreach (DataRow dtrow in dt.Rows)
                {
                    cn = new LoaiMonAnDTO();
                    cn.MaLoai = int.Parse(dtrow["MaLoai"].ToString());
                    cn.TenLoai = dtrow["TenLoai"].ToString();
                    KQ.Add(cn);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return KQ;
        }
        //lay danh sach loai mon an
        public List<LoaiMonAnDTO> LayDanhSachLoaiMonAn(int ma)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<LoaiMonAnDTO> KQ = new List<LoaiMonAnDTO>();
            string sql = "select * from LoaiMonAn where MaLoai="+ma;
            SqlDataAdapter dtadp = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable dt = new DataTable();
                dtadp.Fill(dt);
                LoaiMonAnDTO cn;
                foreach (DataRow dtrow in dt.Rows)
                {
                    cn = new LoaiMonAnDTO();
                    cn.MaLoai = int.Parse(dtrow["MaLoai"].ToString());
                    cn.TenLoai = dtrow["TenLoai"].ToString();
                    KQ.Add(cn);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return KQ;
        }
        //them loai mon an
        public int ThemLoaiMonAn(string TenLoai)
        {
            int MaLoai = this.LayMa("LoaiMonAn", "MaLoai") + 1;
            string sql = "INSERT INTO LoaiMonAn(MaLoai,TenLoai) VALUES(" + MaLoai + ",N'" + TenLoai + "')";
            DataProvider.sqlconn.Open();
            SqlCommand cmd = new SqlCommand(sql, DataProvider.sqlconn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            DataProvider.sqlconn.Close();
            if (kq == 1)
                return 1;
            else
                return 0;
        }
        //cap nhat loai mon an
        public int CapNhatLoaiMonAn(LoaiMonAnDTO mn)
        {
            string sql = "Update LoaiMonAn set TenLoai=N'" + mn.TenLoai + "' where MaLoai=" + mn.MaLoai;
            DataProvider.sqlconn.Open();
            SqlCommand cmd = new SqlCommand(sql, DataProvider.sqlconn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            DataProvider.sqlconn.Close();
            if (kq == 1)
                return 1;
            else
                return 0;
        }
        //xoa loai mon an
        public int XoaLoaiMonAn(int MaLoai)
        {
            string sql = "Delete LoaiMonAn where MaLoai=" + MaLoai;
            DataProvider.sqlconn.Open();
            SqlCommand cmd = new SqlCommand(sql, DataProvider.sqlconn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            DataProvider.sqlconn.Close();
            if (kq == 1)
                return 1;
            else
                return 0;
        }

        // tra cuu mon an theo loai mon an
        public List<LoaiMonAnDTO> layloaimonan()
        {
            string sql = "select * from LoaiMonAn";
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<LoaiMonAnDTO> kq = new List<LoaiMonAnDTO>();
            SqlCommand ds = new SqlCommand(sql, conn);
            SqlDataReader read = ds.ExecuteReader();

            try
            {
                while (read.Read())
                {
                    LoaiMonAnDTO lma = new LoaiMonAnDTO();
                    lma.MaLoai = read.GetInt32(0);
                    lma.TenLoai = read.GetString(1);
                    kq.Add(lma);
                }


            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return kq;
        }
        
        
    }
}
